#! /bin/bash
export LANG=zh_CN.UTF-8
HIVE_HOME=/usr/bin/hive


${HIVE_HOME} -S -e "
drop table if exists zxedu_dwd.fact_customer_relationship;
create table zxedu_dwd.fact_customer_relationship
(   id                          int,
    create_date_time            string,
    update_date_time            string     COMMENT '最后更新时间',
    deleted                     string     COMMENT '是否被删除（禁用）',
    customer_id                 INT        COMMENT '所属客户id',
    first_id                    INT        COMMENT '第一条客户关系id',
    belonger                    INT        COMMENT '归属人',
    belonger_name               string     COMMENT '归属人姓名',
    initial_belonger            INT        COMMENT '初始归属人',
    distribution_handler        INT        COMMENT '分配处理人',
    business_scrm_department_id INT        COMMENT '归属部门',
    last_visit_time             string     COMMENT '最后回访时间',
    next_visit_time             string     COMMENT '下次回访时间',
    origin_type                 string     COMMENT '数据来源',
    itcast_school_id            INT        COMMENT '校区Id',
    itcast_subject_id           INT        COMMENT '学科Id',
    intention_study_type        string     COMMENT '意向学习方式',
    anticipat_signup_date       string     COMMENT '预计报名时间',
    level                       string     COMMENT '客户级别',
    creator                     INT        COMMENT '创建人',
    current_creator             INT        COMMENT '当前创建人：初始==创建人，当在公海拉回时为 拉回人',
    creator_name                string     COMMENT '创建者姓名',
    origin_channel              string     COMMENT '来源渠道',
    COMMENT                     string,
    first_customer_clue_id      INT        COMMENT '第一条线索id',
    last_customer_clue_id       INT        COMMENT '最后一条线索id',
    process_state               string     COMMENT '处理状态',
    process_time                string     COMMENT '处理状态变动时间',
    payment_state               string     COMMENT '支付状态',
    payment_time                string     COMMENT '支付状态变动时间',
    signup_state                string     COMMENT '报名状态',
    signup_time                 string     COMMENT '报名时间',
    notice_state                string     COMMENT '通知状态',
    notice_time                 string     COMMENT '通知状态变动时间',
    lock_state                  string     COMMENT '锁定状态',
    lock_time                   string     COMMENT '锁定状态修改时间',
    itcast_clazz_id             INT        COMMENT '所属ems班级id',
    itcast_clazz_time           string     COMMENT '报班时间',
    payment_url                 string     COMMENT '付款链接',
    payment_url_time            string     COMMENT '支付链接生成时间',
    ems_student_id              INT        COMMENT 'ems的学生id',
    delete_reason               string     COMMENT '删除原因',
    deleter                     INT        COMMENT '删除人',
    deleter_name                string     COMMENT '删除人姓名',
    delete_time                 string     COMMENT '删除时间',
    course_id                   INT        COMMENT '课程ID',
    course_name                 string     COMMENT '课程名称',
    delete_comment              string     COMMENT '删除原因说明',
    close_state                 string     COMMENT '关闭装填',
    close_time                  string     COMMENT '关闭状态变动时间',
    appeal_id                   INT        COMMENT '申诉id',
    tenant                      INT        COMMENT '租户',
    total_fee                   DECIMAL(19)COMMENT '报名费总金额',
    belonged                    INT        COMMENT '小周期归属人',
    belonged_time               string     COMMENT '归属时间',
    belonger_time               string     COMMENT '归属时间',
    transfer                    INT        COMMENT '转移人',
    transfer_time               string     COMMENT '转移时间',
    follow_type                 INT        COMMENT '分配类型，0-自动分配，1-手动分配，2-自动转移，3-手动单个转移，4-手动批量转移，5-公海领取',
    transfer_bxg_oa_account     string     COMMENT '转移到博学谷归属人OA账号',
    transfer_bxg_belonger_name  string     COMMENT '转移到博学谷归属人OA姓名',
    year_code                   string     comment '数据创建年份',
    month_code                  string     comment '数据创建年月',
    date_code                   string     comment '数据创建日期',
    end_date                    string     COMMENT '拉链结束时间')
COMMENT '客户意向表'
partitioned by (start_date string) row format delimited fields terminated by '\t'
stored as orc tblproperties ('orc.compress'='SNAPPY');

drop table if exists zxedu_dwd.fact_customer_appeal;
create table zxedu_dwd.fact_customer_appeal
(   id                             INT          COMMENT '主键',
    customer_relationship_first_id INT          COMMENT '第一条客户关系id',
    employee_id                    INT          COMMENT '申诉人',
    employee_name                  string       COMMENT '申诉人姓名',
    employee_department_id         INT          COMMENT '申诉人部门',
    employee_tdepart_id            INT          COMMENT '申诉人所属部门',
    appeal_status                  INT          COMMENT '申诉状态，0:待稽核 1:无效 2：有效',
    audit_id                       INT          COMMENT '稽核人id',
    audit_name                     string       COMMENT '稽核人姓名',
    audit_department_id            INT          COMMENT '稽核人所在部门',
    audit_department_name          string       COMMENT '稽核人部门名称',
    audit_date_time                string       COMMENT '稽核时间',
    create_date_time               string       COMMENT '创建时间（申诉时间）',
    update_date_time               string       COMMENT '更新时间',
    deleted                        string       COMMENT '删除标志位',
    tenant                         INT,
    end_date                       string       COMMENT '拉链结束日期')
COMMENT '线索申诉信息表'
partitioned by (start_date string)
row format delimited fields terminated by '\t'
stored as orc tblproperties ('orc.compress'='SNAPPY');

drop table if exists zxedu_dwd.dimension_employee;
create table if not exists zxedu_dwd.dimension_employee
(
    id                  INT,
    email               string  COMMENT '公司邮箱，OA登录账号',
    real_name           string  COMMENT '员工的真实姓名',
    phone               string  COMMENT '手机号，目前还没有使用；隐私问题OA接口没有提供这个属性，',
    department_id       string  COMMENT 'OA中的部门编号，有负值',
    department_name     string  COMMENT 'OA中的部门名',
    remote_login        string  COMMENT '员工是否可以远程登录',
    job_number          string  COMMENT '员工工号',
    cross_school        string  COMMENT '是否有跨校区权限',
    last_login_date     string  COMMENT '最后登录日期',
    creator             INT     COMMENT '创建人',
    create_date_time    string  COMMENT '创建时间',
    update_date_time    string  COMMENT '最后更新时间',
    deleted             string  COMMENT '是否被删除（禁用）',
    scrm_department_id  INT     COMMENT 'SCRM内部部门id',
    leave_office        string  COMMENT '离职状态',
    leave_office_time   string  COMMENT '离职时间',
    reinstated_time     string  COMMENT '复职时间',
    superior_leaders_id INT     COMMENT '上级领导ID',
    tdepart_id          INT     COMMENT '直属部门',
    tenant              INT,
    ems_user_name       string,
    end_date            string       COMMENT '拉链结束日期')
COMMENT '员工信息表'
partitioned by (start_date string)
row format delimited fields terminated by '\t'
stored as orc tblproperties ('orc.compress'='SNAPPY');

drop table if exists zxedu_dwd.dimension_scrm_department;
create table if not exists zxedu_dwd.dimension_scrm_department
(
    id               INT                COMMENT '部门id',
    name             string             COMMENT '部门名称',
    parent_id        INT                COMMENT '父部门id',
    create_date_time string             COMMENT '创建时间',
    update_date_time string             COMMENT '更新时间',
    deleted          string             COMMENT '删除标志',
    id_path          string             COMMENT '编码全路径',
    tdepart_code     INT                COMMENT '直属部门',
    creator          string             COMMENT '创建者',
    depart_level     INT                COMMENT '部门层级',
    depart_sign      INT                COMMENT '部门标志，暂时默认1',
    depart_line      INT                COMMENT '业务线，存储业务线编码',
    depart_sort      INT                COMMENT '排序字段',
    disable_flag     INT                COMMENT '禁用标志',
    tenant           INT,
    end_date         string             COMMENT '拉链结束日期')
COMMENT '员工部门表'
partitioned by (start_date string)
row format delimited fields terminated by '\t'
stored as orc tblproperties ('orc.compress'='SNAPPY');

drop table if exists zxedu_dwd.dimension_itcast_school;
create table if not exists zxedu_dwd.dimension_itcast_school
(
    id               INT,
    create_date_time string          COMMENT '创建时间',
    update_date_time string          COMMENT '最后更新时间',
    deleted          string          COMMENT '是否被删除（禁用）',
    name             string          COMMENT '校区名称',
    code             string,
    tenant           INT,
    end_date         string          COMMENT '拉链结束日期')
COMMENT '校区信息表'
partitioned by (start_date string)
row format delimited fields terminated by '\t'
stored as orc tblproperties ('orc.compress'='SNAPPY');

drop table if exists zxedu_dwd.dimension_itcast_subject;
create table if not exists zxedu_dwd.dimension_itcast_subject
(   id               INT,
    create_date_time string           COMMENT '创建时间',
    update_date_time string           COMMENT '最后更新时间',
    deleted          string           COMMENT '是否被删除（禁用）',
    name             string           COMMENT '学科名称',
    code             string,
    tenant           INT,
    end_date         string           COMMENT '拉链结束日期')
COMMENT '学科信息表'
partitioned by (start_date string)
row format delimited fields terminated by '\t'
stored as orc tblproperties ('orc.compress'='SNAPPY');
"